const express = require('express');
const mysql = require('mysql2/promise');
const cors = require('cors');
const bodyParser = require('body-parser');
const multer = require('multer');
const xlsx = require('xlsx');
const path = require('path');
const fs = require('fs');
require('dotenv').config();

const router = express.Router();

const app = express();
const PORT = process.env.PORT || 3666;


// 配置文件上传
const upload = multer({
  dest: 'uploads/',
  limits: { fileSize: 10 * 1024 * 1024 }, // 10MB
  fileFilter: (req, file, cb) => {
    const allowedTypes = ['.xlsx', '.xls', '.csv'];
    const ext = path.extname(file.originalname).toLowerCase();
    cb(allowedTypes.includes(ext) ? null : new Error('只支持Excel (.xlsx, .xls) 和 CSV 文件'), allowedTypes.includes(ext));
  }
});

// IP 白名单配置
const ALLOWED_IPS = ['39.148.102.129', '87.120.252.82','192.168.10.163','127.0.0.1','::1'];

// IP 白名单中间件
app.use((req, res, next) => {
  const clientIP = (req.ip || 
                   req.connection.remoteAddress || 
                   req.socket.remoteAddress || 
                   req.headers['x-forwarded-for']?.split(',')[0]?.trim() ||
                   req.headers['x-real-ip'])?.replace(/^::ffff:/, '') || 'unknown';
  
  console.log(`访问IP: ${clientIP}`);
  
  if (!ALLOWED_IPS.includes(clientIP)) {
    console.log(`× 拒绝访问 - IP ${clientIP} 不在白名单中`);
    return res.status(403).json({ 
      error: '访问被拒绝', 
      message: '。。。。。有礼貌吗你',
      ip: clientIP 
    });
  }
  
  console.log(`√ 允许访问 - IP ${clientIP} 在白名单中`);
  next();
});

// 请求日志中间件
app.use((req, res, next) => {
  const timestamp = new Date().toLocaleString('zh-CN');
  const ip = req.ip || req.connection.remoteAddress || '未知IP';
  
  console.log(`\n[${timestamp}] ${req.method} ${req.url} - 来自: ${ip}`);
  
  if ((req.method === 'POST' || req.method === 'PUT') && req.body && !req.url.includes('/import-excel')) {
    console.log('请求数据:', JSON.stringify(req.body, null, 2));
  }
  next();
});

// 中间件
app.use(cors());
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));

// 数据库连接配置
const dbConfig = {
  host: process.env.DB_HOST || 'localhost',
  user: process.env.DB_USER || 'root', 
  password: process.env.DB_PASSWORD || '111111',
  database: process.env.DB_NAME || '门头沟建筑信息系统',
  charset: 'utf8mb4',
  connectTimeout: 60000,
  port: process.env.DB_PORT || 3306, 
  ssl: false,
  multipleStatements: false,
  supportBigNumbers: true,
  bigNumberStrings: true,
  typeCast: function (field, next) {
    if (field.type === 'BIT' && field.length === 1) {
      return field.buffer()[0] === 1;
    }
    return next();
  }
};

let db;
// 初始化数据库连接
async function initDB() {
  try {
    db = await mysql.createConnection(dbConfig);
    
    // 设置UTF8MB4字符集
    const charsetQueries = [
      "SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'",
      "SET CHARACTER SET utf8mb4",
      "SET character_set_connection = 'utf8mb4'",
      "SET character_set_client = 'utf8mb4'",
      "SET character_set_results = 'utf8mb4'",
      "SET collation_connection = 'utf8mb4_unicode_ci'"
    ];
    
    for (const query of charsetQueries) {
      await db.execute(query);
    }
    
    console.log('门头沟建筑信息系统数据库连接成功');
    console.log('UTF8MB4字符集设置完成');
  } catch (error) {
    console.error('数据库连接失败', error);
    process.exit(1);
  }
}

// ==================== 数据库管理核心功能 ====================

// 1. 获取所有表信息
app.get('/api/database/tables', async (req, res) => {
  try {
    const [tables] = await db.execute('SHOW TABLES');
    const tableList = tables.map(table => Object.values(table)[0]);
    
    const tableDetails = await Promise.all(tableList.map(async (tableName) => {
      try {
        const [columns] = await db.execute(`DESCRIBE \`${tableName}\``);
        const [tableInfo] = await db.execute(
          `SELECT TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?`,
          [dbConfig.database, tableName]
        );
        const [countResult] = await db.execute(`SELECT COUNT(*) as count FROM \`${tableName}\``);
        
        return {
          name: tableName,
          comment: tableInfo[0]?.TABLE_COMMENT || '',
          columns: columns,
          rowCount: countResult[0].count
        };
      } catch (tableError) {
        console.error(`获取表${tableName} 信息失败:`, tableError.message);
        return null;
      }
    }));
    
    res.json({ tables: tableDetails.filter(Boolean) });
  } catch (error) {
    console.error('获取表信息错误', error);
    res.status(500).json({ error: '服务器错误' });
  }
});

// 2. 获取单个表详细信息
app.get('/api/database/tables/:tableName', async (req, res) => {
  try {
    const tableName = decodeURIComponent(req.params.tableName);
    
    const [columns] = await db.execute(`DESCRIBE \`${tableName}\``);
    const [createTable] = await db.execute(`SHOW CREATE TABLE \`${tableName}\``);
    
    res.json({
      tableName,
      columns,
      createStatement: createTable[0]['Create Table']
    });
  } catch (error) {
    console.error('获取表详细信息错误', error);
    res.status(500).json({ error: '服务器错误' + error.message });
  }
});

// 3. 创建新表
app.post('/api/database/tables', async (req, res) => {
  try {
    let { tableName, columns, comment } = req.body;

    // ===== ① 自动添加主键 ID 列 =====
    const hasId = columns.some(c => c.name?.toLowerCase() === 'id');
    if (!hasId) {
      columns.unshift({
        name: 'id',
        type: 'INT',
        notNull: true,
        autoIncrement: true,
        primaryKey: true,
        comment: '主键ID'
      });
    }

    // ===== ② 自动添加固定业务字段 =====
    const extraColumns = [
      { name: '所属区域', type: 'VARCHAR', length: 255, notNull: false, comment: '所属行政区域' },
      { name: '表类型', type: 'VARCHAR', length: 255, notNull: false, comment: '数据表类型' },
    ];

    const existingNames = columns.map(c => c.name);
    extraColumns.forEach(col => {
      if (!existingNames.includes(col.name)) {
        columns.push(col);
      }
    });


    
    // 验证输入
    if (!tableName?.trim()) {
      return res.status(400).json({ error: '表名不能为空' });
    }
    
    if (!Array.isArray(columns) || columns.length === 0) {
      return res.status(400).json({ error: '至少需要定义一个列' });
    }
    
    // 验证列定义
    for (let i = 0; i < columns.length; i++) {
      const col = columns[i];
      if (!col.name?.trim()) {
        return res.status(400).json({ error: `第${i + 1}个列的名称不能为空` });
      }
      if (!col.type?.trim()) {
        return res.status(400).json({ error: `第${i + 1}个列的类型不能为空` });
      }
    }
    
    // 构建CREATE TABLE语句
    let createSQL = `CREATE TABLE \`${tableName}\` (\n`;
    
    // const columnDefs = columns.map(col => {
      // let def = `  \`${col.name}\` ${col.type}`;
      // if (col.length) def += `(${col.length})`;

    const columnDefs = columns.map(col => {

      let typeSQL = ''

      if (col.type === 'VARCHAR') {
        typeSQL = 'VARCHAR(255)'
      } else if (col.type === 'DECIMAL') {
        typeSQL = 'DECIMAL(28,8)'
      } else if (col.type === 'ENUM') {
        typeSQL = `ENUM(${col.length})` // ENUM length 继续来自前端；
      } else {
        typeSQL = col.type
      }

      let def = `  \`${col.name}\` ${typeSQL}`


      if (col.notNull) def += ' NOT NULL';
      if (col.autoIncrement) def += ' AUTO_INCREMENT';
      if (col.defaultValue !== undefined && col.defaultValue !== '') {
        def += col.defaultValue === 'CURRENT_TIMESTAMP' 
          ? ' DEFAULT CURRENT_TIMESTAMP' 
          : ` DEFAULT '${col.defaultValue}'`;
      }
      if (col.comment) def += ` COMMENT '${col.comment}'`;
      
      return def;
    });
    
    createSQL += columnDefs.join(',\n');
    
    // 添加主键
    const primaryKeys = columns.filter(col => col.primaryKey).map(col => col.name);
    if (primaryKeys.length > 0) {
      createSQL += `,\n  PRIMARY KEY (\`${primaryKeys.join('`, `')}\`)`;
    }
    
    createSQL += '\n)';
    if (comment) createSQL += ` COMMENT='${comment}'`;
    createSQL += ' DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;';
    
    console.log('生成的SQL语句:', createSQL);
    
    await db.execute(createSQL);
    res.json({ message: '表创建成功', tableName });
  } catch (error) {
    console.error('创建表错误', error);
    res.status(500).json({ error: '创建表失败 ' + error.message });
  }
});

// 4. 删除表
app.delete('/api/database/tables/:tableName', async (req, res) => {
  try {
    const tableName = decodeURIComponent(req.params.tableName);
    await db.execute(`DROP TABLE \`${tableName}\``);
    res.json({ message: '表删除成功' });
  } catch (error) {
    console.error('删除表错误', error);
    res.status(500).json({ error: '删除表失败' + error.message });
  }
});

// 5. 添加列
app.post('/api/database/tables/:tableName/columns', async (req, res) => {
  try {
    const tableName = decodeURIComponent(req.params.tableName);
    const { columnName, columnType, length, notNull, defaultValue, comment } = req.body;
    
    // let alterSQL = `ALTER TABLE \`${tableName}\` ADD COLUMN \`${columnName}\` ${columnType}`;
    
    // if (length) alterSQL += `(${length})`;
    
    //修复decimal显示不全问题，改为固定28,8
    let typeSQL = ''

    if (columnType === 'VARCHAR') {
      typeSQL = 'VARCHAR(255)'      // 固定255
    } 
    else if (columnType === 'DECIMAL') {
      typeSQL = 'DECIMAL(28,8)'     // 固定28,8
    }
    else if (columnType === 'ENUM') {
      typeSQL = `ENUM(${length})`   // ENUM still uses length
    }
    else {
      typeSQL = columnType          // other types
    }

    let alterSQL = `ALTER TABLE \`${tableName}\` ADD COLUMN \`${columnName}\` ${typeSQL}`;

    if (notNull) alterSQL += ' NOT NULL';
    if (defaultValue !== undefined && defaultValue !== '') {
      alterSQL += defaultValue === 'CURRENT_TIMESTAMP' 
        ? ' DEFAULT CURRENT_TIMESTAMP' 
        : ` DEFAULT '${defaultValue}'`;
    }
    if (comment) alterSQL += ` COMMENT '${comment}'`;
    
    await db.execute(alterSQL);
    res.json({ message: '列添加成功' });
  } catch (error) {
    console.error('添加列错误', error);
    res.status(500).json({ error: '添加列失败' + error.message });
  }
});

// 6. 删除列
app.delete('/api/database/tables/:tableName/columns/:columnName', async (req, res) => {
  try {
    const tableName = decodeURIComponent(req.params.tableName);
    const columnName = decodeURIComponent(req.params.columnName);
    
    await db.execute(`ALTER TABLE \`${tableName}\` DROP COLUMN \`${columnName}\``);
    res.json({ message: '列删除成功' });
  } catch (error) {
    console.error('删除列错误', error);
    res.status(500).json({ error: '删除列失败' + error.message });
  }
});

// 7. 获取表数据

app.get('/api/data/:tableName', async (req, res) => {
  try {
    const tableName = decodeURIComponent(req.params.tableName);
    const { 
      page = 1, 
      limit = 10, 
      search = '', 
      sortField = '', 
      sortOrder = 'ASC',
      searchField = '',
      region = '' // 新增 region 参数
    } = req.query;
    
    const offset = (page - 1) * limit;

    // 构建查询条件
    let whereClause = '';
    const queryParams = [];

    // 1. 优先根据 region 过滤
    if (region) {
      whereClause = ` WHERE \`所属区域\` = ?`;
      queryParams.push(region);
    }

    // 2. 若有搜索字段，追加搜索条件
    if (search && searchField) {
      if (whereClause) {
        whereClause += ` AND \`${searchField}\` LIKE ?`;
      } else {
        whereClause = ` WHERE \`${searchField}\` LIKE ?`;
      }
      queryParams.push(`%${search}%`);
    }

    // 构建排序条件
    let orderClause = '';
    if (sortField) {
      orderClause = ` ORDER BY \`${sortField}\` ${sortOrder}`;
    }

    const query = `SELECT * FROM \`${tableName}\`${whereClause}${orderClause} LIMIT ${parseInt(limit)} OFFSET ${parseInt(offset)}`;
    const countQuery = `SELECT COUNT(*) as total FROM \`${tableName}\`${whereClause}`;

    const [rows] = await db.execute(query, queryParams);
    const [countResult] = await db.execute(countQuery, queryParams);
    const [columns] = await db.execute(`DESCRIBE \`${tableName}\``);

    // 处理 POINT 类型的坐标数据（保持不变）
    const processedRows = await Promise.all(rows.map(async (row) => {
      const processedRow = { ...row };
      if (row.坐标 && Buffer.isBuffer(row.坐标)) {
        try {
          const [pointResult] = await db.execute('SELECT ST_AsText(?) as coords', [row.坐标]);
          processedRow.坐标 = pointResult[0].coords;
        } catch {
          processedRow.坐标 = '[坐标数据]';
        }
      }
      return processedRow;
    }));

    res.json({
      data: processedRows,
      total: countResult[0].total,
      page: parseInt(page),
      limit: parseInt(limit),
      columns: columns
    });
  } catch (error) {
    console.error(`获取表数据错误`, error);
    res.status(500).json({ error: '服务器错误' + error.message });
  }
});

// 8. 新增数据
app.post('/api/data/:tableName', async (req, res) => {
  try {
    const tableName = decodeURIComponent(req.params.tableName);
    const data = req.body;
    
    // 移除自增主键
    delete data.编号;
    delete data.id;
    
    // 坐标字段验证和处理
    if (data.经度 && data.纬度) {
      const lng = parseFloat(data.经度);
      const lat = parseFloat(data.纬度);
      
      if (isNaN(lng) || isNaN(lat) || lng < -180 || lng > 180 || lat < -90 || lat > 90) {
        return res.status(400).json({ error: '经纬度格式错误' });
      }
      
      // 为建筑基础信息表创建POINT坐标
      if (tableName === '建筑基础信息') {
        data.坐标 = `ST_GeomFromText('POINT(${lng} ${lat})')`;
        delete data.经度;
        delete data.纬度;
      }
    }
    
    // 过滤空值
    const filteredData = Object.fromEntries(
      Object.entries(data).filter(([key, value]) => 
        value !== null && value !== undefined && value !== ''
      )
    );
    
    const fields = Object.keys(filteredData);
    const values = Object.values(filteredData);
    
    // 处理POINT坐标
    let query;
    if (filteredData.坐标 && filteredData.坐标.includes('ST_GeomFromText')) {
      const coordIndex = fields.indexOf('坐标');
      const coordValue = values[coordIndex];
      values.splice(coordIndex, 1); // 移除坐标值
      
      const placeholders = fields.map((field, index) => 
        field === '坐标' ? coordValue : '?'
      ).join(',');
      
      query = `INSERT INTO \`${tableName}\` (\`${fields.join('`, `')}\`) VALUES (${placeholders})`;
    } else {
      const placeholders = fields.map(() => '?').join(',');
      query = `INSERT INTO \`${tableName}\` (\`${fields.join('`, `')}\`) VALUES (${placeholders})`;
    }
    
    const [result] = await db.execute(query, values);
    res.json({ id: result.insertId || data.建筑ID, message: '数据创建成功' });
  } catch (error) {
    console.error(`创建数据错误:`, error);
    res.status(500).json({ error: '服务器错误' + error.message });
  }
});

// 9. 更新数据
app.put('/api/data/:tableName/:id', async (req, res) => {
  try {
    const tableName = decodeURIComponent(req.params.tableName);
    const { id } = req.params;
    const data = req.body;
    
    // 移除主键字段
    ['编号', 'id', '建筑ID'].forEach(key => delete data[key]);
    
    // 过滤空值
    const filteredData = Object.fromEntries(
      Object.entries(data).filter(([key, value]) => 
        value !== null && value !== undefined && value !== ''
      )
    );
    
    const fields = Object.keys(filteredData);
    const values = Object.values(filteredData);
    const setClause = fields.map(field => `\`${field}\` = ?`).join(',');
    
    // 自动识别主键字段
    const [columns] = await db.execute(`DESCRIBE \`${tableName}\``);
    const primaryKey = columns.find(col => col.Key === 'PRI');
    const primaryKeyName = primaryKey ? primaryKey.Field : '编号';
    
    const query = `UPDATE \`${tableName}\` SET ${setClause} WHERE \`${primaryKeyName}\` = ?`;
    await db.execute(query, [...values, id]);
    
    res.json({ message: '数据更新成功' });
  } catch (error) {
    console.error(`更新数据错误:`, error);
    res.status(500).json({ error: '服务器错误' + error.message });
  }
});

// 10. 删除数据
app.delete('/api/data/:tableName/:id', async (req, res) => {
  try {
    const tableName = decodeURIComponent(req.params.tableName);
    const { id } = req.params;
    
    // 自动识别主键字段
    const [columns] = await db.execute(`DESCRIBE \`${tableName}\``);
    const primaryKey = columns.find(col => col.Key === 'PRI');
    const primaryKeyName = primaryKey ? primaryKey.Field : '编号';
    
    await db.execute(`DELETE FROM \`${tableName}\` WHERE \`${primaryKeyName}\` = ?`, [id]);
    res.json({ message: '数据删除成功' });
  } catch (error) {
    console.error(`删除数据错误:`, error);
    res.status(500).json({ error: '服务器错误' + error.message });
  }
});

// 11. 行政区划树接口（使用 数据库原始列：区域名称 / 上级区域 / 区域级别）

app.get('/api/regions/tree', async (req, res) => {
  try {
    const { region, type } = req.query;
    let sql = "SELECT 区域名称, 上级区域, 区域级别 FROM `行政区划`";
    const conditions = [];
    const params = [];

    if (region) {
      conditions.push("上级区域 = ?");
      params.push(region);
    }

    if (type) {
      conditions.push("区域级别 = ?");
      params.push(type);
    }

    if (conditions.length > 0) {
      sql += " WHERE " + conditions.join(" AND ");
    }

    sql += " ORDER BY (上级区域 IS NULL) DESC, 上级区域, 区域名称";

    const [rows] = await db.execute(sql, params);

    res.json(rows);
  } catch (error) {
    console.error("获取行政区划树失败:", error);
    res.status(500).json({ error: "服务器错误" });
  }
});

// 12. 更新表信息（表名和备注）
app.post('/api/database/updateTableInfo', async (req, res) => {
  const { oldName, newName, comment } = req.body

  try {
    // 校验表名防注入（只允许中英文、数字、下划线）
    const nameRegex = /^[a-zA-Z0-9_\u4e00-\u9fa5]+$/
    if (!nameRegex.test(oldName) || !nameRegex.test(newName)) {
      return res.status(400).json({ error: '非法表名' })
    }

    // 1. 如果表名改了
    if (oldName !== newName) {
      await db.execute(`RENAME TABLE \`${oldName}\` TO \`${newName}\``)
    }

    // 2. 如果备注不为空，更新备注
    if (comment && comment.trim()) {
      const safeComment = comment.replace(/'/g, "''") // 转义单引号
      const sql = `ALTER TABLE \`${newName}\` COMMENT = '${safeComment}'`
      await db.query(sql)
    }

    res.json({ success: true })
  } catch (error) {
    console.error('更新表信息失败:', error)
    res.status(500).json({ error: '更新表信息失败' })
  }
})

// ================= 统计数据接口 =================
app.get('/api/statistics', async (req, res) => {
  try {
    const [tables] = await db.execute('SHOW TABLES');
    const tableList = tables.map(t => Object.values(t)[0]);

    const statistics = {
      tableCount: 0,
      totalRecords: 0,
      tables: {
        "人员类": { 表数量:0, 记录数:0 },
        "建筑类": { 表数量:0, 记录数:0 },
        "设备类": { 表数量:0, 记录数:0 }
      }
    };

    for (const table of tableList) {
      const parts = table.split('_');
      if (parts.length < 3) continue;

      const type = parts[1];   // 第二段就是类型

      if (!statistics.tables[type]) continue;   // 只认这三类

      const [countResult] = await db.execute(`SELECT COUNT(*) AS count FROM \`${table}\``);
      const count = Number(countResult[0].count);   // 强制数字

      statistics.tables[type].表数量 += 1;
      statistics.tables[type].记录数 += count;

      statistics.totalRecords += count;
      statistics.tableCount += 1;
    }

    res.json(statistics);

  } catch (error) {
    console.error('获取统计数据失败:', error);
    res.status(500).json({ error:'服务器错误' });
  }
});




// ================= 健康检查接口 =================
app.get('/api/health', async (req, res) => {
  try {
    await db.execute('SELECT 1');
    res.json({ status: 'ok' });
  } catch (error) {
    res.status(500).json({ status: 'error' });
  }
});

// ================= 启动服务器 =================
initDB().then(() => {
  app.listen(PORT, () => {
    console.log(`服务器已启动: http://localhost:${PORT}`);
  });
}).catch(err => {
  console.error('初始化数据库失败', err);
});

// ==================== Excel 导入功能（优化版） ====================

// 数据清理函数
function sanitizeForDB(value) {
  if (typeof value !== 'string') return value;
  return value
    .replace(/[\x00-\x08\x0B\x0C\x0E-\x1F\x7F]/g, '')
    .replace(/\uFEFF/g, '')
    .trim();
}

// CSV解析函数
function parseCSVLine(line) {
  const result = [];
  let current = '';
  let inQuotes = false;
  
  for (let i = 0; i < line.length; i++) {
    const char = line[i];
    
    if (char === '"') {
      inQuotes = !inQuotes;
    } else if (char === ',' && !inQuotes) {
      result.push(current.trim());
      current = '';
    } else {
      current += char;
    }
  }
  result.push(current.trim());
  return result;
}

// 字段值提取函数
function getValue(obj, ...keys) {
  for (let key of keys) {
    if (obj[key] !== undefined && obj[key] !== null && obj[key] !== '') {
      return String(obj[key]).trim();
    }
  }
  return '';
}

// 11. Excel/CSV文件导入
app.post('/api/import-excel', upload.single('file'), async (req, res) => {

});

// 12. 获取导入模板
app.get('/api/import-template', async (req, res) => {

});

// ==================== 辅助功能 ====================

// 获取支持的数据类型
app.get('/api/database/data-types', async (req, res) => {
  try {
    const dataTypes = [
      { type: 'BIGINT', category: '数字类', description: '整数数字（无小数）', fixedLength: null },
      { type: 'DECIMAL', category: '数字类', description: '带小数数字（带小数）', fixedLength: '28,8' },
      { type: 'VARCHAR', category: '文本类', description: '普通文字', fixedLength: 255 },
      { type: 'TEXT', category: '文本类', description: '长文本', fixedLength: null },
      { type: 'DATETIME', category: '选择框类', description: '日期时间', fixedLength: null },
      // 唯一开放的
      { type: 'ENUM', category: '选择框类', description: '下拉框', customOptions: true, example: "'选项1','选项2','选项3'" }


      // 原版，勿删：
      // { type: 'BIGINT', category: '数字', description: '数字（无小数）', needsLength: false },
      // { type: 'DECIMAL(28,8)', category: '带小数数字', description: '数字（带小数）', needsLength: false, example: '适用于精度存储' },
      // { type: 'VARCHAR', category: '字符串', description: '文本', needsLength: true, example: '255' },
      // { type: 'TEXT', category: '字符串', description: '长文本', needsLength: false },
      // { type: 'DATETIME', category: '日期时间', description: '日期时间', needsLength: false },
      // { type: 'ENUM', category: '其他', description: '枚举类型', needsLength: true, example: "'选项1','选项2','选项3'" }




      // 暂时废弃，勿删：
      // { type: 'TIMESTAMP', category: '日期时间', description: '时间戳', needsLength: false },
      // { type: 'BOOLEAN', category: '其他', description: '布尔值', needsLength: false },
      // { type: 'JSON', category: '其他', description: 'JSON数据', needsLength: false },
      // { type: 'POINT', category: '坐标', description: 'MySQL几何点类型', needsLength: false, example: '支持空间索引和地理计算' },
      // { type: 'INT', category: '数字', description: '整数', needsLength:  false },
      // { type: 'DECIMAL', category: '数字', description: '精确小数', needsLength: true, example: '10,2' },
      // { type: 'DOUBLE', category: '数字', description: '浮点数', needsLength: false },
      // { type: 'CHAR', category: '字符串', description: '定长字符串', needsLength: true, example: '10' },
      // { type: 'DATE', category: '日期时间', description: '日期', needsLength: false },
    ];
    res.json({ dataTypes });
  } catch (error) {
    console.error('获取数据类型错误:', error);
    res.status(500).json({ error: '服务器错误' });
  }
});

//校验用户输入数据类型是否正确（暂时忽略）


// 获取数据库统计信息
app.get('/api/statistics', async (req, res) => {
  try {
    const [tables] = await db.execute('SHOW TABLES');
    const tableList = tables.map(table => Object.values(table)[0]);
    
    const stats = {
      tableCount: tableList.length,
      tables: {},
      totalRecords: 0
    };
    
    await Promise.all(tableList.map(async (table) => {
      try {
        const [result] = await db.execute(`SELECT COUNT(*) as count FROM \`${table}\``);
        const count = parseInt(result[0].count) || 0;
        stats.tables[table] = count;
        stats.totalRecords += count;
      } catch (error) {
        stats.tables[table] = 0;
      }
    }));
    
    res.json(stats);
  } catch (error) {
    console.error('获取统计数据错误:', error);
    res.status(500).json({ error: '服务器错误' });
  }
});

// 导出表数据
app.get('/api/export/:tableName', async (req, res) => {
  try {
    const tableName = decodeURIComponent(req.params.tableName);
    const [data] = await db.execute(`SELECT * FROM \`${tableName}\``);
    
    // 处理POINT类型坐标数据
    const processedData = await Promise.all(data.map(async (row) => {
      const processedRow = { ...row };
      if (row.坐标 && Buffer.isBuffer(row.坐标)) {
        try {
          const [pointResult] = await db.execute(
            'SELECT ST_AsText(?) as coords, ST_X(?) as lng, ST_Y(?) as lat', 
            [row.坐标, row.坐标, row.坐标]
          );
          processedRow.坐标 = pointResult[0].coords;
          processedRow.经度 = pointResult[0].lng;
          processedRow.纬度 = pointResult[0].lat;
        } catch (error) {
          processedRow.坐标 = '[坐标数据]';
        }
      }
      return processedRow;
    }));
    
    res.setHeader('Content-Type', 'application/json');
    res.setHeader(
      'Content-Disposition',
      `attachment; filename*=UTF-8''${encodeURIComponent(tableName)}_export.json`
    );
    res.json(processedData);
  } catch (error) {
    console.error('导出数据错误:', error);
    res.status(500).json({ error: '服务器错误' });
  }
});

// 批量导入数据
app.post('/api/import/:tableName', async (req, res) => {
  try {
    const tableName = decodeURIComponent(req.params.tableName);
    const { data } = req.body;
    
    if (!Array.isArray(data) || data.length === 0) {
      return res.status(400).json({ error: '无效的导入数据' });
    }
    
    let successCount = 0;
    let errorCount = 0;
    const errors = [];
    
    for (let i = 0; i < data.length; i++) {
      try {
        const record = data[i];
        ['编号', 'id'].forEach(key => delete record[key]);
        
        const fields = Object.keys(record);
        const values = Object.values(record);
        const placeholders = fields.map(() => '?').join(',');
        
        const query = `INSERT INTO \`${tableName}\` (\`${fields.join('`, `')}\`) VALUES (${placeholders})`;
        await db.execute(query, values);
        successCount++;
      } catch (error) {
        errorCount++;
        errors.push({ row: i + 1, error: error.message });
      }
    }
    
    res.json({
      message: '导入完成',
      successCount,
      errorCount,
      errors: errors.slice(0, 10)
    });
  } catch (error) {
    console.error('导入数据错误:', error);
    res.status(500).json({ error: '服务器错误' + error.message });
  }
});

// 健康检查
app.get('/api/health', (req, res) => {
  res.json({ 
    status: 'OK', 
    message: '门头沟建筑信息系统运行正常',
    timestamp: new Date().toISOString()
  });
});

// 创建上传目录
const uploadsDir = path.join(__dirname, 'uploads');
if (!fs.existsSync(uploadsDir)) {
  fs.mkdirSync(uploadsDir, { recursive: true });
}

// 启动服务器
async function startServer() {
  await initDB();
  
  app.listen(PORT, '0.0.0.0', () => {
    console.log(`- 门头沟建筑信息系统服务器运行在 http://0.0.0.0:${PORT}`);
    console.log('- 为前端提供完整的数据库管理API服务');
    console.log('- 已启用请求日志 - 所有API调用都会显示');
    console.log('- 已启用Excel文件导入功能');
    console.log('\n---主要功能---');
    console.log('   - 查看所有表格和结构');
    console.log('   - 创建/删除表格 ');
    console.log('   - 添加/删除列 ');
    console.log('   - 数据增删改查 ');
    console.log('   - 分页、搜索、排序');
    console.log('   - 数据导入导出');
    console.log('   - Excel/CSV文件导入');
    console.log('   - 数据库统计信息');
    console.log('   - POINT坐标类型支持');
    console.log('\n---核心API接口---');
    console.log('   GET  /api/database/tables        - 获取所有表');
    console.log('   POST /api/database/tables        - 创建新表');
    console.log('   GET  /api/data/:tableName        - 查询表数据');
    console.log('   POST /api/data/:tableName        - 新增数据');
    console.log('   PUT  /api/data/:tableName/:id    - 更新数据');
    console.log('   DELETE /api/data/:tableName/:id  - 删除数据');
    console.log('   POST /api/import-excel           - Excel文件导入');
    console.log('   GET  /api/import-template        - 获取导入模板');
    console.log('   GET /api/regions/tree            - 获取行政区划树 ')
  });
}

startServer().catch(console.error);